from flask_records.decorators import query_by_page, query
from common.utils.utils import custom_marshal_pagination

GET_BY_PAGE_SQL = """
select *
from message
where (is_active = :is_active or :is_active is null)
  and (
            lower(message.title) like lower(:search_str_re) or
            lower(message.content) like lower(:search_str_re) or
            :search_str_re is null
    ) 
    and (receiver_id = :receiver_id or :receiver_id is null)
    and (sender_id = :sender_id or :sender_id is null)
 
"""


def get_by_page(page, page_size, sorted_by, sorted_by_type, search_str,  **kwargs):
    sql = GET_BY_PAGE_SQL
    order_by_sql = "{} ORDER BY {} {}".format(sql, sorted_by, sorted_by_type)

    if search_str:
        search_str_re = '%{}%'.format(search_str)
    else:
        search_str_re = None

    @query_by_page(order_by_sql, page_size=page_size)
    def _get_by_pagination(
            page, search_str_re, receiver_id, sender_id, is_active=None, is_delete=None):
        pass

    pagination = _get_by_pagination(
        page=page, search_str_re=search_str_re, **kwargs
    )
    pagination = custom_marshal_pagination(pagination)
    return pagination


def get_num_of_unread(receiver_id):
    sql = """
select count(id)
from message
where message.is_read = false
  and (receiver_id = :receiver_id or :receiver_id is null)
        """

    @query(sql)
    def _get(receiver_id):
        pass
    aa = _get(receiver_id)
    ab = list(aa)
    return ab[0].count
